# 使用提醒:
# 1. xbot包提供软件自动化、数据表格、Excel、日志、AI等功能
# 2. package包提供访问当前应用数据的功能，如获取元素、访问全局变量、获取资源文件等功能
# 3. 当此模块作为流程独立运行时执行main函数
# 4. 可视化流程中可以通过"调用模块"的指令使用此模块

import xbot
from xbot import print, sleep
from . import package
from .package import variables as glv

import requests

def get_data(submitter):
    url = 'https://mock.jsont.run/6zA7NH6ciqxNxGYzKO-Zx'
    res = requests.get(url)
    movies_data = res.json()['data']
    # 统计出票房总数最高的三个国家
    country_box_office = {}
    rating_intervals = {
        '3.0-3.5': 0,
        '9.0-9.5': 0,
        '无评分': 0
    }
    for movie in movies_data:
        movie_country = movie['制片地区']
        movie_money = int(movie['票房'])
        movie_rating = movie.get('评分', '无评分')
        if movie_country in country_box_office:
            country_box_office[movie_country] += movie_money
        else:
            country_box_office[movie_country] = movie_money
        # print(movie_rating)
        if movie_rating == '-':
            rating_intervals['无评分'] += movie_money
        elif 3.0 <= float(movie_rating) < 3.5:
            rating_intervals['3.0-3.5'] += movie_money
        elif 9.0 <= float(movie_rating) < 9.5:
            rating_intervals['9.0-9.5'] += movie_money
    # 打印每个制片地区的票房总和
    # 找出票房最高的三个国家
    result = []
    # 在每个数据开头都加入submitter
    top_countries = sorted(country_box_office.items(), key=lambda x: x[1], reverse=True)[:3]
    final_rating_intervals = [(submitter, interval, total_money) for interval, total_money in rating_intervals.items()]
    top_country_list = []
    for country, total_money in top_countries:
        top_country_list.append((submitter, country, total_money))
    result.append(top_country_list)
    result.append(final_rating_intervals)
    print(result)
    return result


import pymysql


def insert_movie(movie_data):
    try:
        # 连接数据库
        conn = pymysql.connect(host='43.143.30.32',
                               port=3306,
                               user='yingdao',
                               password='9527',
                               database='ydtest',
                               charset='utf8')
        my_cursor = conn.cursor()

        # sql语句
        insert_sql = """
                     INSERT INTO result (提交人, 信息, 票房总数)
                     VALUES (%s, %s, %s) \
                     """

        for movie in movie_data:
            try:
                my_cursor.execute(insert_sql, movie)
                conn.commit()
                print(f"{movie}数据插入成功")
            except Exception as e:
                print(e)
                conn.rollback()
                print(f"{movie}数据插入失败")
    except Exception as e:
        print(e)

    finally:
        if my_cursor is not None:
            my_cursor.close()
        if conn is not None:
            conn.close()


def main(submitter):
    result_data = get_data(submitter)
    insert_movie(result_data[0])
    insert_movie(result_data[1])